package org.neframework.webtest.util.office.excel.util;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;

import com.tmsps.neframework.core.utils.ChkTools;

public class ExcelCheckTools {
	// 格式化日期
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 功能：验证导入的excel 字符串长度，和number类型数据
	 * 
	 * @param list
	 *            要验证的数据
	 * @param map
	 *            ：key 为需要验证的excel 列， val > 0 则验证字符串长度 ; val == 0,则验证是否为number
	 * @return 符合ture 反之false
	 */
	public static boolean checkStringLenOrNum(List<Map<String, Object>> list, Map<String, Integer> ruleMap) {
		boolean flag = false;
		for (Map<String, Object> map : list) {
			for (String key : map.keySet()) {
				if (ChkTools.isNotNull(ruleMap) && ruleMap.containsKey(key)) {
					String val = (String) map.get(key);
					int len = ruleMap.get(key);
					if (len == 0) {
						flag = isNumber(val);
					} else {
						flag = validateStrByLength(val, len);
					}
					if (flag == false)
						return flag;
				} else {
					return false;
				}
			}

		}
		return flag;
	}

	/**
	 * 功能：验证字符串长度是否符合要求，
	 * 
	 * @param strParameter
	 *            要验证的字符串
	 * @param limitLength
	 *            验证的长度
	 * @return 符合长度ture 超出范围false
	 */
	public static boolean validateStrByLength(String strParameter, int limitLength) {

		// int temp_int = 0;
		// byte[] b = strParameter.getBytes();
		//
		// for (int i = 0; i < b.length; i++) {
		// if (b[i] >= 0) {
		// temp_int = temp_int + 1;
		// } else {
		// temp_int = temp_int + 1;
		// i++;
		// }
		// }
		if (ChkTools.isNull(strParameter)) {
			strParameter = "";
		}

		if (strParameter.length() < limitLength) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 功能：正则表达式数字验证
	 * 
	 * @param strParameter
	 *            要验证的字符串
	 * @return 符合长度ture 超出范围false
	 */
	public static boolean isNumber(String str) {
		java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("[0-9,.]*");
		java.util.regex.Matcher match = pattern.matcher(str);
		if (match.matches() == false) {
			return false;
		} else {
			return true;
		}
	}

	public static boolean isNull(List<Object> cont) {
		if (ChkTools.isNull(cont)) {
			return true;
		}
		for (Object object : cont) {
			if (object == null || ChkTools.isNull(object) || ChkTools.isNull(object.toString().trim())) {
				continue;
			} else {
				return false;
			}
		}
		return true;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getStringCellValue(HSSFCell cell) {
		if (cell == null) {
			return "";
		}
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}

		return strCell;
	}

	/**
	 * 获取单元格数据内容为日期类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	@SuppressWarnings("deprecation")
	public String getDateCellValue(HSSFCell cell) {
		String result = "";
		try {
			int cellType = cell.getCellType();
			if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
				Date date = cell.getDateCellValue();
				result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1) + "-" + date.getDate();
			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
				String date = getStringCellValue(cell);
				result = date.replaceAll("[年月]", "-").replace("日", "").trim();
			} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
				result = "";
			}
		} catch (Exception e) {
			System.out.println("日期格式不正确!");
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式

					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();

					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);
				}
				// 如果是纯数字
				else {
					cellvalue = getValueByExcelFunc(cell);
				}
				break;
			}
			// 如果当前Cell的Type为STRIN
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			// 默认的Cell值
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;

	}

	// 读取excel函数的值

	public static String getValueByExcelFunc(HSSFCell cell) {

		FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
		evaluator.evaluateFormulaCell(cell);
		CellValue cellValue = evaluator.evaluate(cell);

		String value = "";

		switch (cellValue.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			value = cellValue.getBooleanValue() + "";
			break;
		case Cell.CELL_TYPE_NUMERIC:
			value = cellValue.getNumberValue() + "";
			break;
		case Cell.CELL_TYPE_STRING:
			value = cellValue.getStringValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			break;
		case Cell.CELL_TYPE_ERROR:
			break;

		// CELL_TYPE_FORMULA will never happen
		case Cell.CELL_TYPE_FORMULA:
			break;
		}
		return value;
	}

	public static String getValue(Object value) {
		String textValue = "";
		if (value == null)
			return textValue;

		if (value instanceof Boolean) {
			boolean bValue = (Boolean) value;
			textValue = "是";
			if (!bValue) {
				textValue = "否";
			}
		} else if (value instanceof Date) {
			Date date = (Date) value;

			textValue = sdf.format(date);
		} else
			textValue = value.toString();

		return textValue;
	}

	public static void main(String[] args) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		for (int i = 0; i < 5; i++) {
			Map<String, Object> m = new HashMap<String, Object>();
			m.put("name", "威尼商人");
			m.put("sex", "");
			m.put("age", "");
			m.put("salary", "3000.50");
			list.add(m);
		}

		Map<String, Integer> map = new HashMap<String, Integer>();
		map.put("name", 5);
		map.put("sex", 2);
		map.put("age", 0);

		System.err.println(checkStringLenOrNum(list, map));

		System.out.println(isNumber(""));
	}
}
